HQSOFT ERP - [AVN] - Cập nhập rank KH

SELECT t.part AS TypeCustomer INTO #TypeCustomer

FROM dbo.AR_KeyBrand WITH(NOLOCK)

CROSS APPLY dbo.fr_SplitStringMAX(TypeCustomer,',') AS t------Check keybrand

WHERE Code = 'KB'

SELECT t.part AS Industry INTO #Industry

FROM dbo.AR_KeyBrand WITH(NOLOCK)

CROSS APPLY dbo.fr_SplitStringMAX(BrandCode,',') AS t--Check keybrand

WHERE Code = 'KB'

SELECT Industry,Rank1,Rank2,Rank3,Rank1Cal,Rank2Cal,Rank3Cal, t.part AS CompCode INTO #BrandByRank

FROM dbo.AR_BrandByRank WITH(NOLOCK)

CROSS APPLY dbo.fr_SplitStringMAX(CompCode,',') AS t---- add công thức tính Rank

SELECT p.Date,p.BranchID,c.CustId,p.Industry, p.QtyKg,p.BrandAVN,

CASE WHEN p.QtyKg >= b.Rank1 THEN '01'

WHEN p.QtyKg >= b.Rank2 AND p.QtyKg < b.Rank1 THEN '02'

WHEN p.QtyKg > 0 AND p.QtyKg < b.Rank3 THEN '03' END AS [Rank]

,

CASE WHEN p.QtyKg >= b.Rank1 THEN 'Rank A'

WHEN p.QtyKg >= b.Rank2 AND p.QtyKg < b.Rank1 THEN 'Rank B'

WHEN p.QtyKg > 0 AND p.QtyKg < b.Rank3 THEN 'Rank C' END AS [RankName] INTO #Data

FROM dbo.AR_CustomerUseProductHis p WITH(NOLOCK)

INNER JOIN AR_Customer c WITH(NOLOCK) ON p.CustID = c.CustId AND p.BranchID = c.BranchID

INNER JOIN #TypeCustomer ct ON ct.TypeCustomer = c.ShopType

INNER JOIN #Industry i ON i.Industry = p.Industry

INNER JOIN #BrandByRank b ON b.Industry = p.Industry AND p.BrandAVN = b.CompCode

WHERE p.QtyKg > 0-- AND p.BrandByRank = ''

UPDATE c SET c.BrandByRank = d.Rank, c.BrandByRankName = d.RankName ---UPDATE QUERY

FROM #Data d INNER JOIN dbo.AR_CustomerUseProductHis c  ON c.BranchID = d.BranchID AND c.CustID = d.CustID AND c.Industry = d.Industry AND c.Date = d.Date

DROP TABLE #TypeCustomer

DROP TABLE #Industry

DROP TABLE #BrandByRank

DROP TABLE #Data